package com.google.code.garbagecan.cassandrastudy.astyanax;

import com.netflix.astyanax.AstyanaxContext;
import com.netflix.astyanax.Keyspace;
import com.netflix.astyanax.connectionpool.OperationResult;
import com.netflix.astyanax.cql.CqlStatementResult;
import com.netflix.astyanax.model.*;
import com.netflix.astyanax.serializers.IntegerSerializer;
import com.netflix.astyanax.serializers.StringSerializer;

public class CQLTest {

	private static final String SQL_CREATE_COLUMN_FAMILY =
			"CREATE TABLE myusers(id int, username text, password text, email text, PRIMARY KEY (id))";

	private static final String SQL_DROP_COLUMN_FAMILY = "DROP TABLE myusers";

	public static void main(String[] args) throws Exception {
//		createColumnFamily();
//		dropColumnFamily();

//		insert();
//		insertWithPreparedCQL();

		list();
		find();

//		delete();
	}

	public static void createColumnFamily() throws Exception {
		AstyanaxContext<Keyspace> context = DBUtils.getContext();
		context.start();
		Keyspace keyspace = context.getClient();

		OperationResult<CqlStatementResult> result =
				keyspace.prepareCqlStatement().withCql(SQL_CREATE_COLUMN_FAMILY).execute();

		context.shutdown();
	}

	public static void dropColumnFamily() throws Exception {
		AstyanaxContext<Keyspace> context = DBUtils.getContext();
		context.start();
		Keyspace keyspace = context.getClient();

		OperationResult<CqlStatementResult> result =
				keyspace.prepareCqlStatement().withCql(SQL_DROP_COLUMN_FAMILY).execute();

		context.shutdown();
	}

	public static void insert() throws Exception {
		AstyanaxContext<Keyspace> context = DBUtils.getContext();
		context.start();
		Keyspace keyspace = context.getClient();

		ColumnFamily<Integer, String> cf = new ColumnFamily<Integer, String>("myusers", IntegerSerializer.get(), StringSerializer.get());
		keyspace.prepareCqlStatement().withCql("insert into myusers(id, username, password, email) values (1, 'kongxx', 'letmein', 'myemail')").execute();
		keyspace.prepareCqlStatement().withCql("insert into myusers(id, username, password, email) values (2, 'mandy', 'letmein', 'myemail')").execute();

		context.shutdown();
	}

	public static void insertWithPreparedCQL() throws Exception {
		AstyanaxContext<Keyspace> context = DBUtils.getContext();
		context.start();
		Keyspace keyspace = context.getClient();

		ColumnFamily<Integer, String> cf = new ColumnFamily<Integer, String>("myusers", IntegerSerializer.get(), StringSerializer.get());
		keyspace.prepareQuery(cf)
				.withCql("insert into myusers(id, username, password, email) values (?, ?, ?, ?)")
				.asPreparedStatement()
				.withIntegerValue(3)
				.withStringValue("myname")
				.withStringValue("mypassword")
				.withStringValue("myemail")
				.execute();

		context.shutdown();
	}

	public static void delete() throws Exception {
		AstyanaxContext<Keyspace> context = DBUtils.getContext();
		context.start();
		Keyspace keyspace = context.getClient();

		ColumnFamily<Integer, String> cf = new ColumnFamily<Integer, String>("myusers", IntegerSerializer.get(), StringSerializer.get());
		keyspace.prepareCqlStatement().withCql("delete from myusers where id=1").execute();
		keyspace.prepareCqlStatement().withCql("delete from myusers where id=2").execute();
		keyspace.prepareCqlStatement().withCql("delete from myusers where id=3").execute();

		context.shutdown();
	}

	public static void list() throws Exception {
		AstyanaxContext<Keyspace> context = DBUtils.getContext();
		context.start();
		Keyspace keyspace = context.getClient();

		ColumnFamily<Integer, String> cf = new ColumnFamily<Integer, String>("myusers", IntegerSerializer.get(), StringSerializer.get());
		OperationResult<CqlResult<Integer, String>> result = keyspace.prepareQuery(cf).withCql("select * from myusers").execute();
		Rows<Integer, String> rows = result.getResult().getRows();
		for (Row<Integer, String> row : rows) {
			ColumnList<String> columns = row.getColumns();
			for (Column column : columns) {
				if (column.getName().equals("id")) {
					System.out.println(column.getName() + ": " + column.getIntegerValue());
				} else {
					System.out.println(column.getName() + ": " + column.getStringValue());
				}
			}
		}

		context.shutdown();
	}

	public static void find() throws Exception {
		AstyanaxContext<Keyspace> context = DBUtils.getContext();
		context.start();
		Keyspace keyspace = context.getClient();

		ColumnFamily<Integer, String> cf = new ColumnFamily<Integer, String>("myusers", IntegerSerializer.get(), StringSerializer.get());
		OperationResult<CqlResult<Integer, String>> result = keyspace.prepareQuery(cf).withCql("select * from myusers where id=1").execute();
		Rows<Integer, String> rows = result.getResult().getRows();
		for (Row<Integer, String> row : rows) {
			ColumnList<String> columns = row.getColumns();
			for (Column column : columns) {
				if (column.getName().equals("id")) {
					System.out.println(column.getName() + ": " + column.getIntegerValue());
				} else {
					System.out.println(column.getName() + ": " + column.getStringValue());
				}
			}
		}

		context.shutdown();
	}
}
